package csv;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import com.mysql.jdbc.DatabaseMetaData;
import com.mysql.jdbc.Driver;

public class CsvFileBuilder
{
	static class IndexInfo
	{
		Map<String, String> indexName = new HashMap<String, String>();
		Set<String> UniqueIndex = new HashSet<String>();
	}

	public static IndexInfo showIndex(ResultSet rs3) throws SQLException
	{
		IndexInfo indexInfo = new IndexInfo();
		while (rs3.next())
		{
			if (rs3.getString(6).equalsIgnoreCase("PRIMARY") == false)
			{
				indexInfo.indexName.put(rs3.getString(9), rs3.getString(6));
				if (rs3.getString(4).equalsIgnoreCase("false"))
				{
					indexInfo.UniqueIndex.add(rs3.getString(6));
				}
			}
			// System.out.println("数据库名: "+ rs3.getString(1));
			// System.out.println("表模式: "+ rs3.getString(2));
			// System.out.println("表名称: "+ rs3.getString(3));
			// System.out.println("索引值是否可以不唯一: "+ rs3.getString(4));
			// System.out.println("索引类别: "+ rs3.getString(5));
			// System.out.println("索引名称: "+ rs3.getString(6));
			// System.out.println("索引类型: "+ rs3.getString(7));
			// System.out.println("索引中的列序列号: "+ rs3.getString(8));
			// System.out.println("列名称: "+ rs3.getString(9));
			// System.out.println("列排序序列: "+ rs3.getString(10));
			// System.out.println("TYPE为 tableIndexStatistic时它是表中的行数否则它是索引中唯一值的数量: "+
			// rs3.getString(11));
			// System.out.println("TYPE为 tableIndexStatisic时它是用于表的页数否则它是用于当前索引的页数: "+
			// rs3.getString(12));
			// System.out.println("过滤器条件: "+ rs3.getString(13));
		}
		rs3.close();
		return indexInfo;
	}

	public static void build(String dbUrl, String outPutUrl) throws InstantiationException, IllegalAccessException, SQLException, IOException
	{
		Class<?> driverClz = null;
		try
		{
			driverClz = Class.forName("com.mysql.jdbc.Driver");
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}

		Driver driver = (Driver) driverClz.newInstance();
		Properties prop = new Properties();
		prop.setProperty("user", "root");
		prop.setProperty("password", "root");
		java.sql.Connection con = driver.connect(dbUrl, prop);

		// java.sql.DatabaseMetaData md = con.getMetaData();
		// md.getSchemas();
		Statement smt = con.createStatement();
		ResultSet rs = smt.executeQuery("show tables");

		BufferedWriter bfw = null;
		while (rs.next())
		{
			try
			{

				String tableName = rs.getString(1);
				String clzName = "";
				for (String str : tableName.split("_"))
				{
					if (!str.equals("t") && !str.equals("game") && !str.equals("pub"))
						clzName = clzName + str.substring(0, 1).toUpperCase() + str.substring(1, str.length());
				}

				clzName += "Bean";

				// if (!tableName.startsWith("t"))
				// {
				// continue;
				// }

				bfw = new BufferedWriter(new FileWriter(outPutUrl + "\\" + clzName + ".csv"));
				// File f = new File(outPutUrl+"\\"+tableName+".csv");
				// if(f.exists())
				// {
				// f.delete();
				// }
				// f.createNewFile();
				bfw.write(tableName);
				bfw.write(",,,,,,,,,,");
				bfw.write("\n");
				bfw.write(",,,,,,,,,,");
				bfw.write("\n");
				bfw.write("Name,Description,Java Type,Data Type,Primary Key,Nullable,Default,Auto Increment,Unique Key,Non-Persistent,Table,index,isUnique");
				bfw.write("\n");
				DatabaseMetaData dbMetaData = (DatabaseMetaData) con.getMetaData();
				String catalog = con.getCatalog(); // catalog 其实也就是数据库名
				ResultSet rs3 = dbMetaData.getIndexInfo(catalog, null, tableName, false, true);
				IndexInfo indexInfo = showIndex(rs3);
				Statement smt2 = con.createStatement();
				ResultSet rs2 = smt2.executeQuery("desc " + tableName);
				while (rs2.next())
				{
					int index = 1;
					String keyword = rs2.getString(index);
					bfw.write(keyword); // pkID
					index++;
					bfw.write(",,");
					String dateType = rs2.getString(index);
					index++;
					if (dateType.startsWith("int"))
					{
						bfw.write("int");
					}
					else if (dateType.startsWith("datetime"))
					{
						bfw.write("Date");
					}
					else if (dateType.startsWith("varchar"))
					{
						bfw.write("String");
					}
					else if (dateType.startsWith("blob"))
					{
						bfw.write("byte[]");
					}
					else if (dateType.startsWith("tinyint"))
					{
						bfw.write("byte");
					}
					else if (dateType.startsWith("bigint"))
					{
						bfw.write("long");
					}
					else if (dateType.startsWith("smallint"))
					{
						bfw.write("short");
					}
					else if (dateType.startsWith("float"))
					{
						bfw.write("float");
					}
					else if (dateType.startsWith("double"))
						bfw.write("double");
					else if (dateType.startsWith("mediumint"))
					{
						bfw.write("short");
					}
					else if (dateType.startsWith("mediumblob"))
					{
						bfw.write("byte[]");
					}
					else if (dateType.startsWith("date"))
					{
						bfw.write("Date");
					}
					else if (dateType.startsWith("text"))
					{
						bfw.write("String");
					}
					else if (dateType.startsWith("bit"))
					{
						bfw.write("boolean");
					}
					else if (dateType.startsWith("timestamp"))
					{
						bfw.write("Date");
					}
					else
					{
						bfw.write("String");
						System.out.println("unsign type " + dateType + "=====" + tableName + "====" + keyword);
					}
					bfw.write(",");
					bfw.write(dateType);
					bfw.write(",");
					String nullable = rs2.getString(index);
					index++;
					String isKey = rs2.getString(index);
					index++;

					if (isKey.startsWith("PRI"))
					{
						bfw.write("Y");
					}
					bfw.write(",");
					if (nullable.startsWith("NO"))
					{
						bfw.write("N");
					}
					bfw.write(",");
					String defaultStr = rs2.getString(index);
					index++;
					if (defaultStr != null && !defaultStr.startsWith("NULL"))
					{
						bfw.write(defaultStr);
					}
					bfw.write(",");

					String Extra = rs2.getString(index);
					index++;
					if (Extra.startsWith("auto_increment"))
					{
						bfw.write("Y");
					}
					bfw.write(",,,,");
					String indexName = indexInfo.indexName.get(rs2.getString(1));
					if (indexName != null)
					{
						bfw.write(indexName);
						bfw.write(",");
						if (indexInfo.UniqueIndex.contains(indexName))
						{
							bfw.write("Y");
						}
						bfw.write(",");
					}
					else
					{
						bfw.write(",,");
					}

					bfw.write("\n");
				}
				// if (rs2 != null)
				// rs.close();

			}
			finally
			{
				if (bfw != null)
					bfw.close();
			}
		}
	}

	public static void main(String[] args) throws Exception
	{
		build("jdbc:mysql://10.23.1.62:3306/Project4Game_0001", "input");
	}
}
